from django.shortcuts import render
from django.shortcuts import HttpResponse, render, redirect
import pandas as pd
import numpy as np
from django.utils.safestring import mark_safe
from sqlalchemy import create_engine, text
# Create your views here.

engine = create_engine('mysql+pymysql://root:wangjia33@localhost:3306/china_all')
table_name = ['china_map', 'province_temp', 'province_pressure', 'city_temp', 'city_precipitation_top10']
sql_base = 'select * from '
sql = text(sql_base+table_name[0])
connect = engine.connect()
map_data = pd.read_sql(sql, connect)
map_data['temp'] = np.round(map_data['temp']/10, 0)
map_data['wind_speed'] = np.round(map_data['wind_speed']/10, 0)

# 打印前40行查看下数据
# print(map_data.head(40))
months = map_data['month'].unique()

map_data1=dict()
map_data2=dict()
for item in months:
    mydata=map_data[map_data['month']==item]
    data_temp=[]
    data_wind=[]
    for i in mydata.index:
        dict_temp = {}
        dict_temp['name'] = mydata.loc[i,'province']
        dict_temp['value'] = mydata.loc[i,'temp']
        data_temp.append(dict_temp)
        dict_wind = {}
        dict_wind['name'] = mydata.loc[i,'province']
        dict_wind['value'] = mydata.loc[i,'wind_speed']
        data_wind.append(dict_wind)
    map_data1[item]=data_temp
    map_data2[item]=data_wind
# print(map_data1)

#读取折线图数据
sql=text(sql_base+table_name[1])
connect = engine.connect()
temp_province_data=pd.read_sql(sql,connect)
temp_province_data['temp']=np.round(temp_province_data['temp']/10,0)
temp_province_data['temp_forecast']=np.round(temp_province_data['temp_forecast'],0)
provinces=list(temp_province_data['province'].unique())

line_data={}
for item in provinces:
    temp_dict={}
    temp_province=temp_province_data[temp_province_data['province']==item]
    temp_dict['month'] = list(temp_province['month'].values)
    temp_dict['temp'] = list(temp_province['temp'].values)
    temp_dict['temp_forecast'] = list(temp_province['temp_forecast'].values)
    line_data[item]=temp_dict
# print(line_data)

# 读取树图数据
sql=text(sql_base+table_name[2])
connect = engine.connect()
pressure_data=pd.read_sql(sql, connect)
pressure_data['pressure']=pressure_data['pressure']/10
# 打印前5行查看下数据
# print(pressure_data.head())

tree_data=dict()
for item in months:
    mydata=pressure_data[pressure_data['month']==item]
    pressure_month=[]
    for i in mydata.index:
            pressure=dict()
            pressure['name']=mydata.loc[i,'province']
            pressure['value'] = mydata.loc[i, 'pressure']
            pressure_month.append(pressure)
    tree_data[item]=pressure_month
# print(tree_data)

# 读取云图数据
sql=text(sql_base+table_name[3])
connect = engine.connect()
city_temp_data=pd.read_sql(sql,connect)
city_temp_data['temp']=city_temp_data['temp']/10
city_temp_data=city_temp_data.dropna()
# print(city_temp_data.head())

word_data=dict()
for item in months:
    mydata=city_temp_data[city_temp_data['month']==item]
    temp_month=[]
    for i in mydata.index:
        temperature=dict()
        temperature['name']=mydata.loc[i,'city']
        temperature['value'] = mydata.loc[i,'temp']
        temp_month.append(temperature)
    word_data[item]=temp_month
# print(word_data)

#读取条形图数据
sql=text(sql_base+table_name[4])
connect = engine.connect()
precipitation_data=pd.read_sql(sql,connect)
precipitation_data['precipitation_6']=precipitation_data['precipitation_6']/10
# print(precipitation_data.head())


bar_data=dict()
for item in months:
    mydata=precipitation_data[precipitation_data['month']==item]
    # mydata=mydata.sort values('precipitation 6')
    precipitation_month={}
    precipitation_month['city'] = list(mydata['city'].values)
    precipitation_month['precipitation'] = list(mydata['precipitation_6'].values)
    bar_data[item] = precipitation_month
# print(bar_data)

def login(request):
    return render(request, 'index.html',
                  {
                      'map_data1': mark_safe(map_data1),
                      'map_data2': mark_safe(map_data2),
                      'provinces': mark_safe(provinces),
                      'line_data': mark_safe(line_data),
                      'tree_data': mark_safe(tree_data),
                      'word_data': mark_safe(word_data),
                      'bar_data': mark_safe(bar_data),
                  })
def map_sample(request):
    return render(request, '地图对照模板.html')

def word_sample(request):
    return render(request, '词云对照模板.html')
